package cn.changeforyou.generator.db.mysql;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/***
 * 优化获取表信息可以利用如下的sql
 * SELECT
 *     TABLE_SCHEMA AS '库名',
 *     TABLE_NAME AS '表名',
 *     COLUMN_NAME AS '列名',
 *     ORDINAL_POSITION AS '列的排列顺序',
 *     COLUMN_DEFAULT AS '默认值',
 *     IS_NULLABLE AS '是否为空',
 *     DATA_TYPE AS '数据类型',
 *     CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',
 *     NUMERIC_PRECISION AS '数值精度(最大位数)',
 *     NUMERIC_SCALE AS '小数精度',
 *     COLUMN_TYPE AS 列类型,
 *     COLUMN_KEY 'KEY',
 *     EXTRA AS '额外说明',
 *     COLUMN_COMMENT AS '注释'
 * FROM
 *     information_schema.`COLUMNS`
 * WHERE
 *     TABLE_SCHEMA = 'waimai'
 * ORDER BY
 *     TABLE_NAME,
 *     ORDINAL_POSITION;
 */
public class DatabaseUtil {
	private final static Logger LOGGER = LoggerFactory.getLogger(DatabaseUtil.class);
	
	private static final String DRIVER = "com.mysql.jdbc.Driver";
	private static final String URL = "jdbc:mysql://39.102.45.4/waimai?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai";
	private static final String USERNAME = "root";
	private static final String PASSWORD = "123456-abc";
	
	private static final String SQL = "SELECT * FROM ";// 数据库操作
	
	static {
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {
			LOGGER.error("can not load jdbc driver", e);
		}
	}
	
	public static void main(String[] args) {
		getConnection(URL, USERNAME, PASSWORD);
		List<String> tableNames = getTableNames2();
		System.out.println("tableNames:" + tableNames);
		for (String tableName : tableNames) {
			System.out.println("=============== " + tableName + "==========================" );
			System.out.println("ColumnNames:" + getColumnNames(tableName));
			System.out.println("ColumnTypes:" + getColumnTypes(tableName));
			System.out.println("ColumnComments:" + getColumnComments(tableName));
		}
		closeConnection(conn);
	}
	
	static Connection conn ;
	
	/**
	 * 获取数据库连接
	 *
	 * @return
	 */
	public static Connection getConnection(String URL, String USERNAME, String PASSWORD) {
		if(null == conn) {
			synchronized (DatabaseUtil.class) {
				if(null == conn) {
					try {
						conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
					} catch (SQLException e) {
						LOGGER.error("get connection failure", e);
					}
				}
			}
		}
		return conn;
	}
	
	/**
	 * 关闭数据库连接
	 * @param conn
	 */
	public static void closeConnection(Connection conn) {
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				LOGGER.error("close connection failure", e);
			}
		}
	}
	
	/**
	 * 获取数据库下的所有表名
	 */
	public static List<String> getTableNames2() {
		List<String> tableNames = new ArrayList<>();
		ResultSet rs = null;
		try {
			//获取数据库的元数据
			Statement statement = conn.createStatement();
			statement.execute("show table status");
			rs = statement.getResultSet();
			//从元数据中获取到所有的表名
			while(rs.next()) {
				tableNames.add(rs.getString("name"));
			}
		} catch (SQLException e) {
			LOGGER.error("getTableNames failure", e);
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				LOGGER.error("close ResultSet failure", e);
			}
		}
		return tableNames;
	}
	
	/**
	 * 获取数据库下的所有表名
	 */
	public static List<String> getTableNames() {
		List<String> tableNames = new ArrayList<>();
		ResultSet rs = null;
		try {
			//获取数据库的元数据
			DatabaseMetaData db = conn.getMetaData();
			//从元数据中获取到所有的表名
			rs = db.getTables(null, null, null, new String[] { "TABLE" });
			while(rs.next()) {
				tableNames.add(rs.getString(3));
			}
		} catch (SQLException e) {
			LOGGER.error("getTableNames failure", e);
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				LOGGER.error("close ResultSet failure", e);
			}
		}
		return tableNames;
	}
	
	/**
	 * 获取表中所有字段名称
	 * @param tableName 表名
	 * @return
	 */
	public static List<String> getColumnNames(String tableName) {
		List<String> columnNames = new ArrayList<>();
		//与数据库的连接
		Connection conn = getConnection(null , null, null);
		PreparedStatement pStemt = null;
		String tableSql = SQL + tableName;
		try {
			pStemt = conn.prepareStatement(tableSql);
			//结果集元数据
			ResultSetMetaData rsmd = pStemt.getMetaData();
			//表列数
			int size = rsmd.getColumnCount();
			for (int i = 0; i < size; i++) {
				columnNames.add(rsmd.getColumnName(i + 1));
			}
		} catch (SQLException e) {
			LOGGER.error("getColumnNames failure", e);
		} finally {
			if (pStemt != null) {
				try {
					pStemt.close();
				} catch (SQLException e) {
					LOGGER.error("getColumnNames close pstem and connection failure", e);
				}
			}
		}
		return columnNames;
	}
	
	/**
	 * 获取表中所有字段类型
	 * @param tableName
	 * @return
	 */
	public static List<String> getColumnTypes(String tableName) {
		List<String> columnTypes = new ArrayList<>();
		//与数据库的连接
		PreparedStatement pStemt = null;
		String tableSql = SQL + tableName;
		try {
			pStemt = conn.prepareStatement(tableSql);
			//结果集元数据
			ResultSetMetaData rsmd = pStemt.getMetaData();
			//表列数
			int size = rsmd.getColumnCount();
			for (int i = 0; i < size; i++) {
				columnTypes.add(rsmd.getColumnTypeName(i + 1));
			}
		} catch (SQLException e) {
			LOGGER.error("getColumnTypes failure", e);
		} finally {
			if (pStemt != null) {
				try {
					pStemt.close();
				} catch (SQLException e) {
					LOGGER.error("getColumnTypes close pstem and connection failure", e);
				}
			}
		}
		return columnTypes;
	}
	
	/**
	 * 获取表中字段的所有注释
	 * @param tableName
	 * @return
	 */
	public static List<String> getColumnComments(String tableName) {
		List<String> columnTypes = new ArrayList<>();
		//与数据库的连接
		PreparedStatement pStemt = null;
		String tableSql = SQL + tableName;
		List<String> columnComments = new ArrayList<>();//列名注释集合
		ResultSet rs = null;
		try {
			pStemt = conn.prepareStatement(tableSql);
			rs = pStemt.executeQuery("show full columns from " + tableName);
			while (rs.next()) {
				columnComments.add(rs.getString("Comment"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("getColumnComments close ResultSet and connection failure", e);
				}
			}
		}
		return columnComments;
	}

}